package com.lswms.modules.lswms.dao.recipient;

import java.math.BigDecimal;
import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.lswms.common.dao.BaseDao;
import com.lswms.modules.lswms.entity.recipient.RecipientsEntity;

/**
 * ${comments}
 *
 * @author DX sunlightcs@gmail.com
 * @since 1.0.0 2021-07-19
 */
@Mapper
public interface RecipientsDao extends BaseDao<RecipientsEntity> {

	
	@Select(value="  select lswms_Recipients.* , b.qty inventoryqty,c.listname recipienttypestr ,d.listname statusstr,e.descr , e.factorycdoe factorycdoe,f.listname boxstr   , e.isuniquecode isuniquecode  \n"
			+ " from lswms_Recipients  left join     lswms_inventory b  on  lswms_Recipients.lot = b.lot and  lswms_Recipients.loc = b.location and  lswms_Recipients.tray = b.tray and  lswms_Recipients.box  = b.box\n"
			+ "   left join lswms_codelist c on recipienttype = c.list and c.code='RECIPIENTTYPE'\n"
			+ "      left join lswms_codelist d on  lswms_Recipients.status = d.list and d.code='RECIPIENTSTATUS'\n"
			+ "	    left join lswms_codelist f on  lswms_Recipients.box = f.list and f.code='BOXLIST'\n"
			+ "		left join lswms_sku e on  lswms_Recipients.sku = e.sku    ${ew.customSqlSegment} ")
	List<RecipientsEntity> selectListBySql(@Param(Constants.WRAPPER)QueryWrapper<RecipientsEntity> queryWrapper);

	
	@Select(value="select sum(a.qty  - isnull(b.backqty,0 )) qty from lswms_Recipients  a left join (  select recipientkey,sum(backqty) backqty from  lswms_Recipientsback group by recipientkey )b\n"
			+ " on  a.recipientkey = b.recipientkey  \n"
			+ " where  a.recipienttype='BORROW' and a.cardId=#{cardId} ")
	BigDecimal selectHasCount(  @Param("cardId")String cardId);

	@Select(value =" select a.* ,isnull(b.backqty,0) backqty ,c.listname recipienttypestr ,d.listname statusstr,e.descr , e.factorycdoe factorycdoe, e.isuniquecode isuniquecode,"
			+ "  f.lot1 ,f.lot2 , f.lot3 , f.lot4 , f.lot5 , f.lot6 , f.lot7 , f.lot8 , f.lot9 , f.lot10 , f.lot11 , f.lot12 , f.lot13 , f.lot14 , f.lot15  \n"
			+ " from lswms_Recipients a left join \n"
			+ " (select recipientkey , sum( isnull(backqty,0)) backqty from lswms_Recipientsback group by recipientkey )\n"
			+ " b on a.recipientkey = b.recipientkey \n"
			+ " left join lswms_codelist c on a.recipienttype = c.list and c.code='RECIPIENTTYPE' "
			+ "	left join lswms_codelist d on a.status = d.list and d.code='RECIPIENTSTATUS'"
			+ "	left join lswms_sku e on a.sku = e.sku  "
			+ "	left join lswms_lot f on a.lot = f.lot   ${ew.customSqlSegment}")
	IPage<RecipientsEntity> selectPageBySql(IPage<RecipientsEntity> page, @Param(Constants.WRAPPER)QueryWrapper<RecipientsEntity> wrapper);

	@Select(value =" select a.* ,isnull(b.backqty,0) backqty ,c.listname recipienttypestr ,d.listname statusstr,e.descr , e.factorycdoe factorycdoe, e.isuniquecode isuniquecode,"
			+ "  f.lot1 ,f.lot2 , f.lot3 , f.lot4 , f.lot5 , f.lot6 , f.lot7 , f.lot8 , f.lot9 , f.lot10 , f.lot11 , f.lot12 , f.lot13 , f.lot14 , f.lot15  \n"
			+ " from lswms_Recipients a left join \n"
			+ " (select recipientkey , sum( isnull(backqty,0)) backqty from lswms_Recipientsback group by recipientkey )\n"
			+ " b on a.recipientkey = b.recipientkey \n"
			+ " left join lswms_codelist c on a.recipienttype = c.list and c.code='RECIPIENTTYPE' "
			+ "	left join lswms_codelist d on a.status = d.list and d.code='RECIPIENTSTATUS'"
			+ "	left join lswms_sku e on a.sku = e.sku  "
			+ "	left join lswms_lot f on a.lot = f.lot   ${ew.customSqlSegment}")
	List<RecipientsEntity> selectListBySql1(@Param(Constants.WRAPPER)QueryWrapper<RecipientsEntity> wrapper);
	
}